Generated code - Using dynamic lists, Adapter
Preface
LLBLGen Pro offers you to create lists in code, without the necessity of the designer. This sometimes can become handy if you just want to
pull a small list of data from the database without having to re-generate the code again. The following paragraph discusses briefly how to create a
dynamic list in code. Dynamic lists are using the same building blocks as Typed View and Typed List classes use and can be used with
normal filters and other constructs like group by.
LLBLGen Pro v2.0's different ResultsetFields classes
In LLBLGen Pro v1.0.2005.1 and earlier, the generated ResultsetFields class in the HelperClasses namespace contained a lot of overloads of the
DefineField() method. By default, LLBLGen Pro still generates this code, however also offers a new way to define fields in a ResultsetFields object
which is more efficient, namely instead of using the FieldIndex enums, it uses the generated
entitynameFields classes. If you're creating a new
project with LLBLGen Pro you can avoid having all the DefineField overloads in the ResultsetFields class, by moving the BackwardsCompatibility templatebindings
to the bottom of the list in the Generator configuration dialog. Please see:
Designer - Generating code. By default the BackwardsCompatibility template bindings take precedence
over the more compact newer templates to let people who upgrade existing code to LLBLGen Pro v2.0.
The examples in this section use the new approach with DefineFields(field object, ...);
Creating dynamic lists
Typed lists are great, however sometimes you need a small list of data, build from one or more entities and use it in a read-only way, and you don't really
need the typed functionality coming with a typed list. After all, a typed list requires you to go into the designer, create the list and re-generate the code.
Dynamic lists are based on entity fields, using the similar code as TypedList classes use internally. These lists are loaded into DataTable objects.
The following example shows you how to create such a dynamic list. The example uses
aggregates and a GroupByCollection to read a custom resultset into a DataTable, fully build with entity fields. Because you can use the same logic Typed Lists
use internally, fetching the data is simply calling a method in a class which is already available.
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "AmountEmployees", "Employee", AggregateFunction.Count);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeId, "Employee", "Manager", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);
' VB.NET
Dim adapter As New DataAccessAdapter()
Dim fields As New ResultsetFields(3)
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 2, "AmountEmployees", "Employee", AggregateFunction.Count)
Dim bucket As IRelationPredicateBucket = New RelationPredicateBucket()
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeId, "Employee", "Manager", JoinHint.None)
Dim groupByClause As IGroupByCollection = New GroupByCollection()
groupByClause.Add(fields(0))
groupByClause.Add(fields(1))
Dim dynamicList As New DataTable()
adapter.FetchTypedList(fields, dynamicList, bucket, 0, Nothing, True, groupByClause)
This list retrieves all managers and the number of employees they manage. Let's walk through the example to make it more understandable.
It first creates a list of fields which will form the list. ResultsetFields is a class defined in the HelperClasses namespace in your generated code
(database generic project) and which is a class derived from EntityFields2, the container for EntityField2 objects which is also located in every entity: the Fields
property.
The three lines following
the declaration of the fields parameter define the three fields in detail. First, it specifies an entity field, to signal which field we want
on that position of the resultset fields, then the index of the field in the resultsetfields object, then the alias for the field in the resultset and
optionally (but we join Employee twice so we have to alias) the alias for the entity this field belongs to. The third field is actually the same as the second,
Employee.LastName, however has an aggregate function applied to it. LastName is not a numeric field, but the type of the field is not important when an aggregate
function is applied, as the field defines a column in the dynamic list and is used as a parameter for the aggregate function; the aggregate function itself, or
better the value it produces, is the actual value of the column and the type is determined at runtime. As a DataColumn object can contain any value, this works
as planned.
As we have to join Employee twice, we have to define a relation collection and add the relation required for the join. The entities in the relation are
properly aliased as "Employee" and "Manager" so the generated code knows from which table the fields should be retrieved. As we're going to group by, we define
the group by collection and add the fields which participate in the group by in the order in which we want to group. We don't add the third field, as it is an
aggregated field which is using the grouped data.
After that, the objects are setup to retrieve the data. We use the adapter's FetchTypedList method, as that
method is capable of fetching a set of data in a DataTable object. We could have specified a filter as well, additional relations for the filter,
and even paging parameters.
This way of creating lists of data is very flexible and can be easily extended with expressions for complex resultsets, for example for usage in reports.
